const pool = require('../config/db');
const {min, ceil} = Math;

const calculate = async(destination, weight) =>{
	const des = await findRootRegion(destination);
	console.log('目的地：', des);
	const deliver = await find_deliver(des, weight);
	console.log('投递重量信息：', deliver);
	const isinside = await is_inside(destination);
	let manage, transport, collection, res;
	[res] = await pool.query('SELECT value FROM const_param WHERE param_name = ?',['collection']);
	collection = res[0].value;
	if(isinside){
		[res] = await pool.query('SELECT value FROM const_param WHERE param_name = ?',['local_manage']);
		manage = res[0].value;
		[res] = await pool.query('SELECT value FROM const_param WHERE param_name = ?',['local_mile']);
		let local_mile = res[0].value;
		transport = weight/1000*local_mile*0.6/1.09;
	}else{
		[res] = await pool.query('SELECT value FROM const_param WHERE param_name = ?',['fore_manage']);
		manage = res[0].value;
		transport = (weight/1000*(35+95)*0.6+weight/1000*800*0.5)/1.09;
	}
	return {'inside': isinside, 'manage_cost': manage, 'transport_cost': transport, 'deliver_cost': deliver.cost /1.06, 'collection_cost': collection};
}

async function find_deliver(destination, weight) {
    let level = 0, base_level = 0, max_level, cost, over;
    try {
        // 第一个查询：获取重量等级
        const [rows] = await pool.query(
            'SELECT max_weight, weight_level, is_over FROM pe_weight_level WHERE destination = ?', 
            [destination]
        );
        rows.sort((a,b) => {a.weight_level > b.weight_level});
        for (const row of rows) {
            const { max_weight, weight_level, is_over } = row;
			if(is_over == 0) base_level++;
			max_level = weight_level;
            if (weight > max_weight) {
                level = weight_level;
            }
        }
        level = Math.min(level+1, max_level);
        over = level > base_level;
		
        // 第二个查询：检查是否超重 - 修复参数传递
        const [res] = await pool.query(
            'SELECT price, weight_level FROM pe_price WHERE destination = ? ', 
            [destination]  // 所有参数放在一个数组中
        );
        // 检查查询结果
        if (!res || res.length === 0) {
            throw new Error('未找到价格信息');
        }
		res.sort((a,b) => {a.weight_level > b.weight_level});
		if (over){
			cost = res[base_level-1].price;
			weight = Math.ceil(weight);
			for (var i = base_level; i < level; i++) {
				if(weight < rows[i].max_weight){
					cost += res[i].price * (weight - rows[i-1].max_weight);
				}else{
					cost += res[i].price * (rows[i].max_weight - rows[i-1].max_weight);
				}
			}
		}else{
			cost = res[level-1].price;
		}
        return {'level': level, 'cost': cost.toFixed(2), 'over': over};
    } catch(error) {
        console.error('数据库错误:', error);
        throw error; // 重新抛出错误以便上层处理
    }
}



async function is_inside(destination){
	try{
		const [province] = await pool.query('SELECT region_name FROM pe_destination_region WHERE destination = ?', [destination]);
		const [local_province] = await pool.query('SELECT destination FROM pe_destination_region WHERE region_name = ?', ['省内']);
		return province[0].region_name === local_province[0].destination;
	}catch (error) {
	    console.error('Database error:', error);
	}
}

async function findRootRegion(destination) {
    // Step 1: 检查当前 destination 是否直接在 pe_price 表中存在
    const [priceRows] = await pool.query(
        'SELECT 1 FROM pe_price WHERE destination = ? LIMIT 1',
        [destination]
    );
    if (priceRows.length > 0) {
        return destination; // 直接命中价格表，返回
    }

    // Step 2: 查询所有所属 region
    const [regionRows] = await pool.query(
        'SELECT region_name, has_par FROM pe_destination_region WHERE destination = ?',
        [destination]
    );
    if (regionRows.length === 0) {
        return null; // 无区域匹配
    }

    // 遍历每个 region，寻找可返回的匹配项
    for (const { region_name: regionName, has_par: hasPar } of regionRows) {
        // 如果 regionName 是 "省内"，立即返回
        if (regionName === '省内') {
            return '省内';
        }

        // 检查 regionName 是否在 pe_price 中（直接可定价）
        const [priceCheck] = await pool.query(
            'SELECT 1 FROM pe_price WHERE destination = ? LIMIT 1',
            [regionName]
        );
        if (priceCheck.length > 0) {
            return regionName; // 直接可定价，短路返回
        }

        // 否则：递归查找上层区域（只有 has_par = 1 才继续）
        if (hasPar === 1) {
            const result = await findRootRegion(regionName); // 递归调用
            // 如果子递归返回了有效结果（如 '省内' 或某个可定价区域）
            if (result !== null) {
                if (result === '省内') {
                    return '省内';
                }
                return result;
            }
        }
		return null;
	}
}

module.exports = {calculate};